import { pgTable, serial, text, integer, timestamp, index } from "drizzle-orm/pg-core";

// One row per page view. Everything the dashboard shows is derived from this table.
export const pageViews = pgTable(
  "page_views",
  {
    id: serial().primaryKey(),
    // Client-generated id for this specific page view, used to attach the
    // "time on page" duration once the visitor leaves the page.
    viewId: text("view_id").notNull().unique(),
    // Groups page views that belong to the same visit (tab session, 30 min idle window).
    sessionId: text("session_id").notNull(),
    // Stable, privacy-friendly visitor id (salted hash of IP) — used for "unique visitors".
    visitorId: text("visitor_id").notNull(),
    path: text("path").notNull(),
    // Full referrer URL as reported by the browser (may be empty for direct visits).
    referrer: text("referrer").notNull().default(""),
    // Human-friendly traffic source: referrer host, or "Direct / Geen" when there is none.
    source: text("source").notNull().default("Direct / Geen"),
    // "mobile" | "tablet" | "desktop"
    device: text("device").notNull().default("desktop"),
    userAgent: text("user_agent").notNull().default(""),
    country: text("country").notNull().default(""),
    // Time the visitor actively spent on this page, filled in when they leave.
    durationMs: integer("duration_ms").notNull().default(0),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    index("page_views_created_at_idx").on(t.createdAt),
    index("page_views_session_idx").on(t.sessionId),
    index("page_views_path_idx").on(t.path),
    index("page_views_visitor_idx").on(t.visitorId),
  ],
);

<script src="/track.js" defer></script>